package com.exam.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import com.exam.model.GroupModel;
import com.exam.model.StudentModel;

public class StudentDao extends BaseDao{

	/**
	 * 登陆检测
	 * @param sName
	 * @param sId
	 * @return
	 * @throws SQLException
	 */
	public boolean checkStudentLogin(String sName , String sId) throws SQLException{
		String sql = "select * from ex_student where stu_id='"+sId+"' and  stu_name='"+sName+"'";
		res = query(sql);
		boolean b = false;
		if(res.next()){
			b = true;
		}
		closeDB();
		
		return b;
		
	}
	
	/**
	 * 获取学生对象
	 */
	public StudentModel getOneStudent(String sId) throws SQLException{
		String sql = "select " +
				"stu_id," +
				"stu_name," +
				"( select gname from ex_group where gid=id ) stu_group " +
				" from ex_student where stu_id='"+sId+"'";
		res = query(sql);
		StudentModel student = null ;
		if(res.next()){
			student = new StudentModel();
			student.setStu_id(res.getString(1));
			student.setStu_name(res.getString(2));
			student.setStu_group(res.getString(3));
		}
		closeDB();
		return student;
		
	}

	/**
	 * 获取学生列表
	 * @param sId 学号
	 * @param sName 姓名
	 * @param limit 分页
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<StudentModel> getStudentList(String sId ,String sName, String limit) throws SQLException{
		ArrayList<StudentModel> list = new ArrayList<StudentModel>();
		String condition = "";
		if(!sId.equals("")){
			condition += " and stu_id='"+sId+"' ";
		}
		if(!sName.equals("")){
			condition += " and stu_name='"+sName+"' ";
		}
		String sql = "select " +
				"stu_id," +
				"stu_name," +
				"g.gname " +
				" from   ex_student ,ex_group g " +
				"where gid=g.id and g.admin='"+getAdmin()+"' "+condition+"  order by stu_id asc "+limit;
		System.out.println(sql);
		res = query(sql);
		StudentModel student = null ;
		while(res.next()){
			student = new StudentModel();
			student.setStu_id(res.getString(1));
			student.setStu_name(res.getString(2));
			student.setStu_group(res.getString(3));
			list.add(student);
		}
		closeDB();
		return list;
	}
	
	/**
	 * 获取用户记录数
	 * @param sId
	 * @param sName
	 * @return
	 */
	public int getCount(String sId ,String sName){
		String condition = "";
		if(!sId.equals("")){
			condition += " and stu_id='"+sId+"' ";
		}
		if(!sName.equals("")){
			condition += " and stu_name='"+sName+"' ";
		}
		String sql = "select count(*) from ex_student where stu_id<>'' "+condition;
		int m = 0;
		res = query(sql);
		try {
			if(res.next()){
				m = res.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(m+" 条记录");
		return m;
		
	}
	
	/**
	 * 添加学生信息
	 * @param student
	 * @return
	 */
	public boolean addOneStudent(StudentModel student){
		String sql = " insert into ex_student ( " +
						"stu_id," +
						"stu_name," +
						"gid ) values ( " +
					"'"+student.getStu_id()+"'," +
					"'"+student.getStu_name()+"'," +
					"'"+student.getGid()+"' ) ";
		return adu(sql);
	}
	
	/**
	 * 更新数据
	 * @param student
	 * @return
	 */
	public boolean updateStudent(StudentModel student){
		String sql = " update ex_student set  " +
				"stu_name='"+student.getStu_name()+"'," +
				"gid='"+student.getGid()+"'" +
				" where stu_id='"+student.getStu_id()+"'";
		return adu(sql);
	}
	
	/**
	 * 删除考生信息
	 * @param sid
	 * @return
	 */
	public boolean deleteStudent(String sid){
		String sql = "delete from ex_student where stu_id='"+sid+"'";
		return adu(sql);
	}
	
	/*----考生分组------------- */
	
	//获取所有分组
	public ArrayList<GroupModel> getAllGroup(String limit) throws SQLException{
		ArrayList<GroupModel> list = new ArrayList<GroupModel>();
		GroupModel gm=null;
		String sql = " select id,admin,gname from ex_group where admin='"+getAdmin()+"' "+limit;
		res = query(sql);
		while(res.next()){
			gm = new GroupModel();
			gm.setId(res.getInt(1));
			gm.setAdmin(res.getString(2));
			gm.setGname(res.getString(3));
			list.add(gm);
		}
		closeDB();
		return list;
	}
	
	//获取所有分组
		public ArrayList<GroupModel> getAllGroup() throws SQLException{
			ArrayList<GroupModel> list = new ArrayList<GroupModel>();
			GroupModel gm=null;
			String sql = " select id,admin,gname from ex_group ";
			res = query(sql);
			while(res.next()){
				gm = new GroupModel();
				gm.setId(res.getInt(1));
				gm.setAdmin(res.getString(2));
				gm.setGname(res.getString(3));
				list.add(gm);
			}
			closeDB();
			return list;
		}
	
	
	//获取分组记录--分页用
	public int getGroupCount() throws SQLException{
		String sql = " select count(*) from ex_group where admin='"+getAdmin()+"'";
		int m = 0;
		res = query(sql);
		if(res.next()){
			m = res.getInt(1);
		}
		closeDB();
		return m;
	}
	
	//新增考生分组
	public boolean addGroup(String gname){
		String sql = " insert into ex_group (gname,admin) values ( '"+gname+"','"+getAdmin()+"' ) ";
		System.out.println(sql);
		return adu(sql);
	}
	
	//获取一条分组信息
	public GroupModel getOneGroup(int gid) throws SQLException{
		String sql = " select id,gname from ex_group where admin='"+getAdmin()+"' and id="+gid;
		res = query(sql);
		GroupModel gm = new GroupModel();
		if(res.next()){
			gm.setId(res.getInt(1));
			gm.setGname(res.getString(2));
		}
		closeDB();
		return gm;
	}
	
	//修改分组
	public boolean updateGroup(int gid , String gname){
		String sql = "update ex_group set gname='"+gname+"' where id="+gid;
		return adu(sql);
	}
	
	//检测分组是否存在 
	public boolean isExistGroup(String gname) throws SQLException{
		boolean b=false;
		String sql = "select id from ex_group where gname='"+gname+"'";
		res = query(sql);
		if(res.next()){
			b=true;
		}
		closeDB();
		return b;
	}
	
	//删除分组 -- 同时删除组内学生
	public boolean deleteGroup(int gid){
		String sql = "delete from ex_student where gid="+gid;
		String sql1 = "delete from ex_group where id="+gid;
		return adu(sql1)&&adu(sql);
	}
	
}
